package org.madmac.setor;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.madmac.exception.SetorNotFoundException;
import org.wakeful.conn.Connection;

public class SetorDao {

   private static SetorDao instance;

   private SetorDao() {
   }

   public static SetorDao getInstance() {

      if (instance == null) {
         instance = new SetorDao();
      }
      return instance;
   }

   /**
    *
    * @return
    * @throws SQLException
    * @throws ClassNotFoundException
    * @throws Exception
    * @deprecated Use findAll
    */
   public List<Setor> selectAll() throws SQLException, ClassNotFoundException, Exception {

      List<Setor> lista = new ArrayList<Setor>();

      java.sql.Connection conn = Connection.getInstance();
      Statement stmt = conn.createStatement();
      StringBuilder builder = new StringBuilder();
      builder.append("SELECT s.id_setor, s.nome, s.ramal, count(m.id_maquina) AS num_maquinas ");
      builder.append("FROM madmac.setor s LEFT JOIN madmac.maquina m ");
      builder.append("ON m.id_setor = s.id_setor ");
      builder.append("GROUP BY s.id_setor, s.nome, s.ramal ");
      builder.append("ORDER BY s.nome");
      String sql = builder.toString();

      ResultSet rs = null;
      try {
         rs = stmt.executeQuery(sql);

         while (rs.next()) {
            Setor setor = new Setor();
            setor.setCodeSetor(rs.getInt("id_setor"));
            setor.setNome(rs.getString("nome"));
            setor.setRamal(rs.getInt("ramal"));
            setor.setNum_maquinas(rs.getInt("num_maquinas"));

            lista.add(setor);
         }
      } catch (Exception e) {
         return null;
      } finally {
         stmt.close();
         if (rs != null) {
            rs.close();
         }
      }
      return lista;
   }

	public Setor findById(int idSetor) throws Exception {
		Setor setor = null;
		PreparedStatement pstmt = null;
      ResultSet rs = null;
		try {
			java.sql.Connection conn = Connection.getInstance();
         pstmt = conn.prepareStatement(SetorQuery.retrieve());
			pstmt.setInt(1, idSetor);
			rs = pstmt.executeQuery();
         if (rs.next()) {
            setor = new Setor();
				setor.setNome(rs.getString("nome"));
				setor.setRamal(rs.getInt("ramal"));
				setor.setIdSetor(rs.getInt("id_setor"));
			}
		} catch (Exception e) {
			throw new Exception(e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
         if (rs != null) {
            rs.close();
         }
		}
		return setor;
	}

   public List<Setor> findAll() throws Exception {
      List<Setor> list = new ArrayList<Setor>();
      Statement stmt = null;
      ResultSet rs = null;
      try {
         java.sql.Connection conn = Connection.getInstance();
         stmt = conn.createStatement();
         rs = stmt.executeQuery(SetorQuery.findAll());
         while (rs.next()) {
            Setor setor = new Setor();
            setor.setIdSetor(rs.getInt("id_setor"));
            setor.setNome(rs.getString("nome"));
            setor.setRamal(rs.getInt("ramal"));
            setor.setNumMaquinas(rs.getInt("num_maquinas"));

            list.add(setor);
         }
      } catch (Exception e) {
			throw new Exception(e);
      } finally {
         if (stmt != null) {
            stmt.close();
         }
         if (rs != null) {
            rs.close();
         }
      }
      return list;
   }

   public Setor selectById(int search_code_setor) throws SQLException, ClassNotFoundException, Exception {

      Setor setor = null;
      java.sql.Connection conn = Connection.getInstance();
      Statement stmt = conn.createStatement();
      String sql = "SELECT * FROM unify.setores WHERE code_setor = " + search_code_setor + " ";

      ResultSet rs = null;
      try {
         rs = stmt.executeQuery(sql);
      } catch (Exception e) {
         return null;
      }

      if (rs.next()) {

         setor = new Setor();

         int code_setor = rs.getInt("code_setor");
         String descricao = rs.getString("nome");
         int ramal = rs.getInt("ramal");

         setor.setCodeSetor(code_setor);
         setor.setNome(descricao);
         setor.setRamal(ramal);

      } else {

         throw new SetorNotFoundException();
      }
      return setor;
   }

	public Object create(Setor setor) throws Exception {
		Integer id = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;
		try {
			java.sql.Connection conn = Connection.getInstance();
         pstmt = conn.prepareStatement(SetorQuery.create());
			pstmt.setString(1, setor.getNome());
			pstmt.setObject(2, setor.getRamal(), Types.INTEGER);
			rs = pstmt.executeQuery();
         if (rs.next()) {
            id = rs.getInt("id_setor");
         }
		} catch (Exception e) {
			throw new Exception(e);
      } finally {
         if (pstmt != null) {
            pstmt.close();
         }
         if (rs != null) {
            rs.close();
         }
		}
		return id;
	}

   public boolean insertSala(Setor setor) throws SQLException, ClassNotFoundException, Exception {

      java.sql.Connection conn = Connection.getInstance();
      Statement stmt = conn.createStatement();
      String sql = "INSERT INTO unify.setores (nome, ramal) ";
      sql += "VALUES ('" + setor.getNome() + "', " + setor.getRamal() + ") ";
      int count = stmt.executeUpdate(sql);
      boolean resultado = false;
      if (count == 1) {
         resultado = true;
      }
      return resultado;
   }

   public void deleteSala(int del_code_setor) throws SQLException, ClassNotFoundException, Exception {

      java.sql.Connection conn = Connection.getInstance();
      Statement stmt = conn.createStatement();
      String sql = "DELETE FROM unify.setores WHERE code_setor = " + del_code_setor + " ";
      stmt.executeUpdate(sql);
   }

   public boolean updateSala(Setor setor, int up_code_setor) throws SQLException, ClassNotFoundException, Exception {

      java.sql.Connection conn = Connection.getInstance();
      Statement stmt = conn.createStatement();
      String sql = "UPDATE unify.setores SET ";
      sql += "nome = '" + setor.getNome() + "', ";
      sql += "ramal = " + setor.getRamal() + " ";
      sql += "WHERE code_setor = " + up_code_setor + " ";
      int count = stmt.executeUpdate(sql);
      boolean resultado = false;
      if (count == 1) {
         resultado = true;
      }
      return resultado;
   }
}
